package lx.dlh;

import com.jfinal.log.Log;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;

import java.text.MessageFormat;
import java.util.Date;
import java.util.List;

public class Statistics {
    private static final Log log = Log.getLog(Statistics.class);

    // 统计活动信息热度_累计
    public static void statsActSum() {
        List<Record> companyIdList = Db.find("select company_id,act_id from stats_act group by company_id,act_id order by company_id,act_id");
        log.info("----查询企业id列表（去重）" + companyIdList.size());
        companyIdList.forEach(companyIdTemp -> {
            String companyId = companyIdTemp.getStr("company_id");
            String actId = companyIdTemp.getStr("act_id");
            String sql = MessageFormat.format("select create_time from stats_act where company_id = {0} and act_id = {1} order by create_time asc", companyId, actId);
            List<Record> statsCompanyList = Db.find(sql);
            int stats_count = 0;
            Date create_time = null;

            for (int i = 0; i < statsCompanyList.size(); i++) {
                Record record = statsCompanyList.get(i);
                stats_count += 1;
                //
                if (i == 0) {
                    create_time = record.getTimestamp("create_time");
                }
            }

            Record statsActSum = new Record().set("company_id", companyIdTemp.getStr("company_id"))
                    .set("stats_count", stats_count).set("act_id", companyIdTemp.getStr("act_id"))
                    .set("create_time", create_time).set("update_time", create_time).set("del_flag", 0);
            log.info(MessageFormat.format("添加stats_act_sum，企业id为：{0}，act_id为：{1}，结果：{2}", companyId, actId, Db.save("stats_act_sum", statsActSum)));
        });
    }

    // 统计团建信息热度_累计
    public static void statsBuildingSum() {
        List<Record> companyIdList = Db.find("select company_id,building_id from stats_building group by company_id,building_id order by company_id,building_id");
        log.info("----查询企业id列表（去重）" + companyIdList.size());
        companyIdList.forEach(companyIdTemp -> {
            String companyId = companyIdTemp.getStr("company_id");
            String buildingId = companyIdTemp.getStr("building_id");
            String sql = MessageFormat.format("select create_time from stats_building where company_id = {0} and building_id = {1} order by create_time asc", companyId, buildingId);
            List<Record> statsCompanyList = Db.find(sql);
            int stats_count = 0;
            Date create_time = null;

            for (int i = 0; i < statsCompanyList.size(); i++) {
                Record record = statsCompanyList.get(i);
                stats_count += 1;
                //
                if (i == 0) {
                    create_time = record.getTimestamp("create_time");
                }
            }

            Record statsBuildingSum = new Record().set("company_id", companyIdTemp.getStr("company_id"))
                    .set("stats_count", stats_count).set("building_id", companyIdTemp.getStr("building_id"))
                    .set("create_time", create_time).set("update_time", create_time).set("del_flag", 0);
            log.info(MessageFormat.format("添加stats_building_sum，企业id为：{0}，act_id为：{1}，结果：{2}", companyId, buildingId, Db.save("stats_building_sum", statsBuildingSum)));
        });
    }

    // 统计企业热度_累计
    public static void statsCompanySum() {
        List<Record> companyIdList = Db.find("select distinct company_id from stats_company");
        log.info("统计企业热度_累计----查询企业id列表（去重）" + companyIdList.size());
        companyIdList.forEach(companyIdTemp -> {
            String sql = MessageFormat.format("select create_time,stats_type from stats_company where company_id = {0} order by create_time asc", companyIdTemp.getStr("company_id"));
            List<Record> statsCompanyList = Db.find(sql);
            int stats_count = 0;
            int stats_count_pc = 0;
            //int stats_count_applet = 0;
            Date create_time = null;

            for (int i = 0; i < statsCompanyList.size(); i++) {
                Record record = statsCompanyList.get(i);
                stats_count += 1;
                // 统计类型（1pc 2小程序+App）
                if (record.getInt("stats_type") == 1) {
                    stats_count_pc += 1;
                } else if (record.getInt("stats_type") == 2) {
                    //stats_count_applet += 1;
                }
                //
                if (i == 0) {
                    create_time = record.getTimestamp("create_time");
                }
            }
            // 判断company_id是否存在，存在就更新，不存在就添加
            Record statsCompanySumByQu = Db.findById("stats_company_sum", "company_id", companyIdTemp.getStr("company_id"));
            if (null == statsCompanySumByQu) {
                Record statsCompanySum = new Record().set("company_id", companyIdTemp.getStr("company_id"))
                        .set("stats_count", stats_count).set("stats_count_pc", stats_count_pc)
                        .set("create_time", create_time).set("update_time", create_time).set("del_flag", 0);
                log.info(MessageFormat.format("添加stats_company_sum，企业id为：{0}，添加结果：{1}", companyIdTemp.getStr("company_id"), Db.save("stats_company_sum", statsCompanySum)));
            } else {
                Integer id = statsCompanySumByQu.getInt("id");
                Integer statsCount = statsCompanySumByQu.getInt("stats_count") + stats_count;
                Integer statsCountPc = statsCompanySumByQu.getInt("stats_count_pc") + stats_count_pc;
                int count = Db.update("update stats_company_sum set stats_count=?, stats_count_pc=?  where id = ?", statsCount, statsCountPc, id);
                log.info(MessageFormat.format("添加stats_company_sum，企业id为：{0}，更新结果：{1}", companyIdTemp.getStr("company_id"), count));
            }
        });
    }

    // 统计企业应用下载量_累计
    public static void statsCompanyApplicationSum() {
        List<Record> companyIdList = Db.find("select company_id,application_id from stats_company_application group by company_id,application_id order by company_id,application_id");
        log.info("----查询企业id列表（去重）" + companyIdList.size());
        companyIdList.forEach(companyIdTemp -> {
            String companyId = companyIdTemp.getStr("company_id");
            String applicationId = companyIdTemp.getStr("application_id");
            String sql = MessageFormat.format("select create_time from stats_company_application where company_id = {0} and application_id = {1} order by create_time asc", companyId, applicationId);
            List<Record> statsCompanyList = Db.find(sql);
            int stats_count = 0;
            Date create_time = null;

            for (int i = 0; i < statsCompanyList.size(); i++) {
                Record record = statsCompanyList.get(i);
                stats_count += 1;
                //
                if (i == 0) {
                    create_time = record.getTimestamp("create_time");
                }
            }

            Record statsCompanyApplicationSum = new Record().set("company_id", companyIdTemp.getStr("company_id"))
                    .set("stats_count", stats_count).set("application_id", companyIdTemp.getStr("application_id"))
                    .set("create_time", create_time).set("update_time", create_time).set("del_flag", 0);
            log.info(MessageFormat.format("添加stats_company_application_sum，企业id为：{0}，act_id为：{1}，结果：{2}", companyId, applicationId, Db.save("stats_company_application_sum", statsCompanyApplicationSum)));
        });
    }

    // 统计企业宣传片播放量_累计
    public static void statsCompanyVideoSum() {
        List<Record> companyIdList = Db.find("select company_id,video_id from stats_company_video group by company_id,video_id order by company_id,video_id");
        log.info("----查询企业id列表（去重）" + companyIdList.size());
        companyIdList.forEach(companyIdTemp -> {
            String companyId = companyIdTemp.getStr("company_id");
            String videoId = companyIdTemp.getStr("video_id");
            String sql = MessageFormat.format("select create_time from stats_company_video where company_id = {0} and video_id = {1} order by create_time asc", companyId, videoId);
            List<Record> statsCompanyList = Db.find(sql);
            int stats_count = 0;
            Date create_time = null;

            for (int i = 0; i < statsCompanyList.size(); i++) {
                Record record = statsCompanyList.get(i);
                stats_count += 1;
                //
                if (i == 0) {
                    create_time = record.getTimestamp("create_time");
                }
            }

            Record statsCompanyVideoSum = new Record().set("company_id", companyIdTemp.getStr("company_id"))
                    .set("stats_count", stats_count).set("video_id", companyIdTemp.getStr("video_id"))
                    .set("create_time", create_time).set("update_time", create_time).set("del_flag", 0);
            log.info(MessageFormat.format("添加stats_company_video_sum，企业id为：{0}，act_id为：{1}，结果：{2}", companyId, videoId, Db.save("stats_company_video_sum", statsCompanyVideoSum)));
        });
    }

    // 统计招募信息热度_累计
    public static void statsCooperateSum() {
        List<Record> companyIdList = Db.find("select company_id,cooperate_id from stats_cooperate group by company_id,cooperate_id order by company_id,cooperate_id");
        log.info("----查询企业id列表（去重）" + companyIdList.size());
        companyIdList.forEach(companyIdTemp -> {
            String companyId = companyIdTemp.getStr("company_id");
            String cooperateId = companyIdTemp.getStr("cooperate_id");
            String sql = MessageFormat.format("select create_time from stats_cooperate where company_id = {0} and cooperate_id = {1} order by create_time asc", companyId, cooperateId);
            List<Record> statsCompanyList = Db.find(sql);
            int stats_count = 0;
            Date create_time = null;

            for (int i = 0; i < statsCompanyList.size(); i++) {
                Record record = statsCompanyList.get(i);
                stats_count += 1;
                //
                if (i == 0) {
                    create_time = record.getTimestamp("create_time");
                }
            }

            Record statsCooperateSum = new Record().set("company_id", companyIdTemp.getStr("company_id"))
                    .set("stats_count", stats_count).set("cooperate_id", companyIdTemp.getStr("cooperate_id"))
                    .set("create_time", create_time).set("update_time", create_time).set("del_flag", 0);
            log.info(MessageFormat.format("添加stats_cooperate_sum，企业id为：{0}，act_id为：{1}，结果：{2}", companyId, cooperateId, Db.save("stats_cooperate_sum", statsCooperateSum)));
        });
    }

    // 统计云工厂热度_累计
    public static void statsFactorySum() {
        List<Record> companyIdList = Db.find("select company_id,factory_id from stats_factory group by company_id,factory_id order by company_id,factory_id");
        log.info("----查询企业id列表（去重）" + companyIdList.size());
        companyIdList.forEach(companyIdTemp -> {
            String companyId = companyIdTemp.getStr("company_id");
            String factoryId = companyIdTemp.getStr("factory_id");
            String sql = MessageFormat.format("select create_time from stats_factory where company_id = {0} and factory_id = {1} order by create_time asc", companyId, factoryId);
            List<Record> statsCompanyList = Db.find(sql);
            int stats_count = 0;
            Date create_time = null;

            for (int i = 0; i < statsCompanyList.size(); i++) {
                Record record = statsCompanyList.get(i);
                stats_count += 1;
                //
                if (i == 0) {
                    create_time = record.getTimestamp("create_time");
                }
            }

            Record statsFactorySum = new Record().set("company_id", companyIdTemp.getStr("company_id"))
                    .set("stats_count", stats_count).set("factory_id", companyIdTemp.getStr("factory_id"))
                    .set("create_time", create_time).set("update_time", create_time).set("del_flag", 0);
            log.info(MessageFormat.format("添加stats_factory_sum，企业id为：{0}，act_id为：{1}，结果：{2}", companyId, factoryId, Db.save("stats_factory_sum", statsFactorySum)));
        });
    }

    // 统计企业资料下载量_累计
    public static void statsLibrarySum() {
        List<Record> companyIdList = Db.find("select company_id,library_id from stats_library group by company_id,library_id order by company_id,library_id");
        log.info("----查询企业id列表（去重）" + companyIdList.size());
        companyIdList.forEach(companyIdTemp -> {
            String companyId = companyIdTemp.getStr("company_id");
            String libraryId = companyIdTemp.getStr("library_id");
            String sql = MessageFormat.format("select create_time from stats_library where company_id = {0} and library_id = {1} order by create_time asc", companyId, libraryId);
            List<Record> statsCompanyList = Db.find(sql);
            int stats_count = 0;
            Date create_time = null;

            for (int i = 0; i < statsCompanyList.size(); i++) {
                Record record = statsCompanyList.get(i);
                stats_count += 1;
                //
                if (i == 0) {
                    create_time = record.getTimestamp("create_time");
                }
            }

            Record statsLibrarySum = new Record().set("company_id", companyIdTemp.getStr("company_id"))
                    .set("stats_count", stats_count).set("library_id", companyIdTemp.getStr("library_id"))
                    .set("create_time", create_time).set("update_time", create_time).set("del_flag", 0);
            log.info(MessageFormat.format("添加stats_library_sum，企业id为：{0}，act_id为：{1}，结果：{2}", companyId, libraryId, Db.save("stats_library_sum", statsLibrarySum)));
        });
    }

    // 统计企业动态热度_累计
    public static void statsNewsSum() {
        List<Record> companyIdList = Db.find("select company_id,news_id from stats_news group by company_id,news_id order by company_id,news_id");
        log.info("----查询企业id列表（去重）" + companyIdList.size());
        companyIdList.forEach(companyIdTemp -> {
            String companyId = companyIdTemp.getStr("company_id");
            String newsId = companyIdTemp.getStr("news_id");
            String sql = MessageFormat.format("select create_time from stats_news where company_id = {0} and news_id = {1} order by create_time asc", companyId, newsId);
            List<Record> statsCompanyList = Db.find(sql);
            int stats_count = 0;
            Date create_time = null;

            for (int i = 0; i < statsCompanyList.size(); i++) {
                Record record = statsCompanyList.get(i);
                stats_count += 1;
                //
                if (i == 0) {
                    create_time = record.getTimestamp("create_time");
                }
            }

            Record statsNewsSum = new Record().set("company_id", companyIdTemp.getStr("company_id"))
                    .set("stats_count", stats_count).set("news_id", companyIdTemp.getStr("news_id"))
                    .set("create_time", create_time).set("update_time", create_time).set("del_flag", 0);
            log.info(MessageFormat.format("添加stats_news_sum，企业id为：{0}，act_id为：{1}，结果：{2}", companyId, newsId, Db.save("stats_news_sum", statsNewsSum)));
        });
    }

    // 统计资讯热度_累计
    public static void statsPlatformNewsSum() {
        List<Record> companyIdList = Db.find("select news_id from stats_platform_news group by news_id order by news_id");
        log.info("----查询企业id列表（去重）" + companyIdList.size());
        companyIdList.forEach(companyIdTemp -> {
            String newsId = companyIdTemp.getStr("news_id");
            String sql = MessageFormat.format("select create_time from stats_platform_news where news_id = {0} order by create_time asc", newsId);
            List<Record> statsCompanyList = Db.find(sql);
            int stats_count = 0;
            Date create_time = null;

            for (int i = 0; i < statsCompanyList.size(); i++) {
                Record record = statsCompanyList.get(i);
                stats_count += 1;
                //
                if (i == 0) {
                    create_time = record.getTimestamp("create_time");
                }
            }

            Record statsPlatformNewsSum = new Record()
                    .set("stats_count", stats_count).set("news_id", companyIdTemp.getStr("news_id"))
                    .set("create_time", create_time).set("update_time", create_time).set("del_flag", 0);
            log.info(MessageFormat.format("添加stats_platform_news_sum，act_id为：{0}，结果：{1}", newsId, Db.save("stats_platform_news_sum", statsPlatformNewsSum)));
        });
    }

    // 统计招投标热度_累计
    public static void statsProcurementSum() {
        List<Record> companyIdList = Db.find("select company_id,procurement_id from stats_procurement group by company_id,procurement_id order by company_id,procurement_id");
        log.info("----查询企业id列表（去重）" + companyIdList.size());
        companyIdList.forEach(companyIdTemp -> {
            String companyId = companyIdTemp.getStr("company_id");
            String procurementId = companyIdTemp.getStr("procurement_id");
            String sql = MessageFormat.format("select create_time from stats_procurement where company_id = {0} and procurement_id = {1} order by create_time asc", companyId, procurementId);
            List<Record> statsCompanyList = Db.find(sql);
            int stats_count = 0;
            Date create_time = null;

            for (int i = 0; i < statsCompanyList.size(); i++) {
                Record record = statsCompanyList.get(i);
                stats_count += 1;
                //
                if (i == 0) {
                    create_time = record.getTimestamp("create_time");
                }
            }

            Record statsProcurementSum = new Record().set("company_id", companyIdTemp.getStr("company_id"))
                    .set("stats_count", stats_count).set("procurement_id", companyIdTemp.getStr("procurement_id"))
                    .set("create_time", create_time).set("update_time", create_time).set("del_flag", 0);
            log.info(MessageFormat.format("添加stats_procurement_sum，企业id为：{0}，act_id为：{1}，结果：{2}", companyId, procurementId, Db.save("stats_procurement_sum", statsProcurementSum)));
        });
    }

    // 统计产品热度_累计
    public static void statsProdSum() {
        List<Record> companyIdList = Db.find("select company_id,prod_id from stats_prod group by company_id,prod_id order by company_id,prod_id");
        log.info("----查询企业id列表（去重）" + companyIdList.size());
        companyIdList.forEach(companyIdTemp -> {
            String companyId = companyIdTemp.getStr("company_id");
            String prodId = companyIdTemp.getStr("prod_id");
            String sql = MessageFormat.format("select create_time from stats_prod where company_id = {0} and prod_id = {1} order by create_time asc", companyId, prodId);
            List<Record> statsCompanyList = Db.find(sql);
            int stats_count = 0;
            Date create_time = null;

            for (int i = 0; i < statsCompanyList.size(); i++) {
                Record record = statsCompanyList.get(i);
                stats_count += 1;
                //
                if (i == 0) {
                    create_time = record.getTimestamp("create_time");
                }
            }

            Record statsProdSum = new Record().set("company_id", companyIdTemp.getStr("company_id"))
                    .set("stats_count", stats_count).set("prod_id", companyIdTemp.getStr("prod_id"))
                    .set("create_time", create_time).set("update_time", create_time).set("del_flag", 0);
            log.info(MessageFormat.format("添加stats_prod_sum，企业id为：{0}，act_id为：{1}，结果：{2}", companyId, prodId, Db.save("stats_prod_sum", statsProdSum)));
        });
    }

    // 统计在线项目热度_累计
    public static void statsProjectSum() {
        List<Record> companyIdList = Db.find("select company_id,project_id from stats_project group by company_id,project_id order by company_id,project_id");
        log.info("----查询企业id列表（去重）" + companyIdList.size());
        companyIdList.forEach(companyIdTemp -> {
            String companyId = companyIdTemp.getStr("company_id");
            String projectId = companyIdTemp.getStr("project_id");
            String sql = MessageFormat.format("select create_time from stats_project where company_id = {0} and project_id = {1} order by create_time asc", companyId, projectId);
            List<Record> statsCompanyList = Db.find(sql);
            int stats_count = 0;
            Date create_time = null;

            for (int i = 0; i < statsCompanyList.size(); i++) {
                Record record = statsCompanyList.get(i);
                stats_count += 1;
                //
                if (i == 0) {
                    create_time = record.getTimestamp("create_time");
                }
            }

            Record statsProjectSum = new Record().set("company_id", companyIdTemp.getStr("company_id"))
                    .set("stats_count", stats_count).set("project_id", companyIdTemp.getStr("project_id"))
                    .set("create_time", create_time).set("update_time", create_time).set("del_flag", 0);
            log.info(MessageFormat.format("添加stats_project_sum，企业id为：{0}，act_id为：{1}，结果：{2}", companyId, projectId, Db.save("stats_project_sum", statsProjectSum)));
        });
    }

    // 统计招聘信息热度_累计
    public static void statsRecruitmentSum() {
        List<Record> companyIdList = Db.find("select company_id,recruitment_id from stats_recruitment group by company_id,recruitment_id order by company_id,recruitment_id");
        log.info("----查询企业id列表（去重）" + companyIdList.size());
        companyIdList.forEach(companyIdTemp -> {
            String companyId = companyIdTemp.getStr("company_id");
            String recruitmentId = companyIdTemp.getStr("recruitment_id");
            String sql = MessageFormat.format("select create_time from stats_recruitment where company_id = {0} and recruitment_id = {1} order by create_time asc", companyId, recruitmentId);
            List<Record> statsCompanyList = Db.find(sql);
            int stats_count = 0;
            Date create_time = null;

            for (int i = 0; i < statsCompanyList.size(); i++) {
                Record record = statsCompanyList.get(i);
                stats_count += 1;
                //
                if (i == 0) {
                    create_time = record.getTimestamp("create_time");
                }
            }

            Record statsRecruitmentSum = new Record().set("company_id", companyIdTemp.getStr("company_id"))
                    .set("stats_count", stats_count).set("recruitment_id", companyIdTemp.getStr("recruitment_id"))
                    .set("create_time", create_time).set("update_time", create_time).set("del_flag", 0);
            log.info(MessageFormat.format("添加stats_recruitment_sum，企业id为：{0}，act_id为：{1}，结果：{2}", companyId, recruitmentId, Db.save("stats_recruitment_sum", statsRecruitmentSum)));
        });
    }

    // 统计服务热度_累计
    public static void statsSvrSum() {
        List<Record> companyIdList = Db.find("select company_id,svr_id from stats_svr group by company_id,svr_id order by company_id,svr_id");
        log.info("----查询企业id列表（去重）" + companyIdList.size());
        companyIdList.forEach(companyIdTemp -> {
            String companyId = companyIdTemp.getStr("company_id");
            String svrId = companyIdTemp.getStr("svr_id");
            String sql = MessageFormat.format("select create_time from stats_svr where company_id = {0} and svr_id = {1} order by create_time asc", companyId, svrId);
            List<Record> statsCompanyList = Db.find(sql);
            int stats_count = 0;
            Date create_time = null;

            for (int i = 0; i < statsCompanyList.size(); i++) {
                Record record = statsCompanyList.get(i);
                stats_count += 1;
                //
                if (i == 0) {
                    create_time = record.getTimestamp("create_time");
                }
            }

            Record statsSvrSum = new Record().set("company_id", companyIdTemp.getStr("company_id"))
                    .set("stats_count", stats_count).set("svr_id", companyIdTemp.getStr("svr_id"))
                    .set("create_time", create_time).set("update_time", create_time).set("del_flag", 0);
            log.info(MessageFormat.format("添加stats_svr_sum，企业id为：{0}，act_id为：{1}，结果：{2}", companyId, svrId, Db.save("stats_svr_sum", statsSvrSum)));
        });
    }

    // 统计新技术热度_累计
    public static void statsTechSum() {
        List<Record> companyIdList = Db.find("select company_id,tech_id from stats_tech group by company_id,tech_id order by company_id,tech_id");
        log.info("----查询企业id列表（去重）" + companyIdList.size());
        companyIdList.forEach(companyIdTemp -> {
            String companyId = companyIdTemp.getStr("company_id");
            String techId = companyIdTemp.getStr("tech_id");
            String sql = MessageFormat.format("select create_time from stats_tech where company_id = {0} and tech_id = {1} order by create_time asc", companyId, techId);
            List<Record> statsCompanyList = Db.find(sql);
            int stats_count = 0;
            Date create_time = null;

            for (int i = 0; i < statsCompanyList.size(); i++) {
                Record record = statsCompanyList.get(i);
                stats_count += 1;
                //
                if (i == 0) {
                    create_time = record.getTimestamp("create_time");
                }
            }

            Record statsTechSum = new Record().set("company_id", companyIdTemp.getStr("company_id"))
                    .set("stats_count", stats_count).set("tech_id", companyIdTemp.getStr("tech_id"))
                    .set("create_time", create_time).set("update_time", create_time).set("del_flag", 0);
            log.info(MessageFormat.format("添加stats_tech_sum，企业id为：{0}，act_id为：{1}，结果：{2}", companyId, techId, Db.save("stats_tech_sum", statsTechSum)));
        });
    }
}
